import sqlite3
from datetime import datetime
from threading import Lock
import json

class DBManager:
    _instance = None
    _lock = Lock()
    
    def __new__(cls):
        with cls._lock:
            if cls._instance is None:
                cls._instance = super(DBManager, cls).__new__(cls)
                cls._instance.init_db()
            return cls._instance
    
    def init_db(self):
        """初始化数据库"""
        self.db_path = 'data/charts.db'
        self.init_tables()
    
    def get_connection(self):
        """获取数据库连接"""
        return sqlite3.connect(self.db_path)

    def init_tables(self):
        """初始化表结构"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # 创建图表表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS charts (
                    chart_id TEXT PRIMARY KEY,
                    user_id TEXT NOT NULL,
                    client_id TEXT NOT NULL,
                    name TEXT NOT NULL,
                    symbol TEXT,
                    content TEXT NOT NULL,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建自选股票表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS favorite_stocks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT NOT NULL,
                    stock_code TEXT NOT NULL,
                    stock_name TEXT NOT NULL,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(user_id, stock_code)
                    UNIQUE(stock_code)
                )
            ''')
            
            # 创建监视列表表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS watchlists (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT NOT NULL,
                    name TEXT NOT NULL UNIQUE,
                    items TEXT,
                    sort_order INTEGER DEFAULT 0
                )
            ''')
            
            conn.commit()

    def save_chart(self, chart_id, user_id, client_id, name, symbol, content):
        """保存或更新图表"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            cursor.execute('''
                INSERT OR REPLACE INTO charts 
                (chart_id, user_id, client_id, name, symbol, content, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (chart_id, user_id, client_id, name, symbol, content, now))
            
            conn.commit()

    def get_chart(self, chart_id, user_id, client_id):
        """获取指定图表"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT chart_id, user_id, client_id, name, symbol, content, created_at, updated_at
                FROM charts
                WHERE chart_id = ? AND user_id = ? AND client_id = ?
            ''', (chart_id, user_id, client_id))
            
            row = cursor.fetchone()
            if row:
                return {
                    'chart_id': row[0],
                    'user_id': row[1],
                    'client_id': row[2],
                    'name': row[3],
                    'symbol': row[4],
                    'content': row[5],
                    'created_at': row[6],
                    'updated_at': row[7]
                }
            return None

    def get_user_charts(self, user_id, client_id):
        """获取用户的所有图表"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT chart_id, user_id, client_id, name, symbol, content, created_at, updated_at
                FROM charts
                WHERE user_id = ? AND client_id = ?
                ORDER BY updated_at DESC
            ''', (user_id, client_id))
            
            return [{
                'chart_id': row[0],
                'user_id': row[1],
                'client_id': row[2],
                'name': row[3],
                'symbol': row[4],
                'content': row[5],
                'created_at': row[6],
                'updated_at': row[7]
            } for row in cursor.fetchall()]

    def delete_chart(self, chart_id, user_id, client_id):
        """删除图表"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                DELETE FROM charts
                WHERE chart_id = ? AND user_id = ? AND client_id = ?
            ''', (chart_id, user_id, client_id))
            
            conn.commit()

    def get_favorite_stocks(self, user_id):
        """获取用户的自选股票"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT stock_code, stock_name, created_at, updated_at
                FROM favorite_stocks
                WHERE user_id = ?
                ORDER BY created_at DESC
            ''', (user_id,))
            
            return [{
                'stock_code': row[0],
                'stock_name': row[1],
                'created_at': row[2],
                'updated_at': row[3]
            } for row in cursor.fetchall()]

    def add_favorite_stock(self, user_id, stock_code, stock_name):
        """添加自选股票"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT OR REPLACE INTO favorite_stocks 
                (user_id, stock_code, stock_name)
                VALUES (?, ?, ?)
            ''', (user_id, stock_code, stock_name))
            conn.commit()

    def remove_favorite_stock(self, user_id, stock_code):
        """删除自选股票"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                DELETE FROM favorite_stocks
                WHERE user_id = ? AND stock_code = ?
            ''', (user_id, stock_code))
            conn.commit() 

    def get_user_watchlists(self, user_id):
        """获取用户的所有监视列表，按order排序"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                '''SELECT id, user_id, name, items, sort_order FROM watchlists WHERE user_id = ? ORDER BY sort_order ASC''',
                (user_id,)
            )
            watchlists = []
            for row in cursor.fetchall():
                watchlist = {
                    'id': row[0],
                    'user_id': row[1],
                    'name': row[2],
                    'items': json.loads(row[3]) if row[3] else [],
                    'sort_order': row[4]
                }
                watchlists.append(watchlist)
            return watchlists

    def save_or_update_watchlist(self, user_id, name, items=None):
        """根据用户id和name更新或新增watchlist"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            print(1214)
            # 检查是否已存在
            cursor.execute('SELECT id, items FROM watchlists WHERE user_id = ? AND name = ?', 
                         (user_id, name))
            existing = cursor.fetchone()
            

            if existing:
                
                # 更新已存在的记录
                if items is not None:
                    print(1216)
                    cursor.execute(
                        '''UPDATE watchlists SET items = ? WHERE id = ?''',
                        (json.dumps(items), existing[0])
                    )
                    

                result = {
                    "id": existing[0],
                    "name": name,
                    "items": items if items is not None else json.loads(existing[1]) if existing[1] else []
                }
                print("heh 2")  
            else:
                # 获取当前最大排序值
                cursor.execute(
                    'SELECT COALESCE(MAX(sort_order), 0) FROM watchlists WHERE user_id = ?',
                    (user_id,)
                )
                max_sort_order = cursor.fetchone()[0]
                
                # 插入新记录
                cursor.execute(
                    '''
                    INSERT INTO watchlists 
                    (user_id, name, items, sort_order) 
                    VALUES (?, ?, ?, ?)
                    ''',
                    (user_id, name, json.dumps(items if items is not None else []), max_sort_order + 1)
                )
                
                new_id = cursor.lastrowid
                result = {
                    "id": new_id,
                    "name": name,
                    "items": items if items is not None else [],
                    "sort_order": max_sort_order + 1
                }
            
            conn.commit()
            return result

    def rename_watchlist(self, user_id, old_name, new_name):
        """根据用户id和old_name重命名watchlist"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # 检查旧名称是否存在
            cursor.execute('SELECT id FROM watchlists WHERE user_id = ? AND name = ?', 
                         (user_id, old_name))
            if not cursor.fetchone():
                raise ValueError(f"Watchlist with name '{old_name}' not found")
            
            # 检查新名称是否已被使用
            cursor.execute('SELECT id FROM watchlists WHERE user_id = ? AND name = ?', 
                         (user_id, new_name))
            if cursor.fetchone():
                raise ValueError(f"Watchlist with name '{new_name}' already exists")
            
            # 更新名称
            cursor.execute(
                'UPDATE watchlists SET name = ? WHERE user_id = ? AND name = ?',
                (new_name, user_id, old_name)
            )
            conn.commit()
            return True

    def delete_watchlist_by_name(self, user_id, name):
        """根据用户id和name删除watchlist"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                'DELETE FROM watchlists WHERE user_id = ? AND name = ?',
                (user_id, name)
            )
            conn.commit()
            return cursor.rowcount > 0

    def reorder_watchlists(self, user_id, order_data):
        """重新排序监视列表"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            for item in order_data:
                cursor.execute(
                    'UPDATE watchlists SET sort_order = ? WHERE id = ? AND user_id = ?',
                    (item['sort_order'], item['id'], user_id)
                )
            conn.commit()